import numpy as np
import pandas as pd

brand_file_path = '../../data/raw data/餐饮连锁品牌数据.xlsx'
cater_file_path = '../../data/raw data/餐饮连锁数据.xlsx'
sheet_names = ['门店信息', '菜品信息', '营销记录', '顾客评价']
sheet_name = sheet_names[0]
df_store = pd.read_excel(cater_file_path, sheet_name)

# ======================================
# 2️⃣ 检查并处理空值
# ======================================
print("\n【空值统计】")
print(df_store.isnull().sum())
print("======================")

# --- 测试1：查看哪些列空值最多 ---
missing_rate = df_store.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")

# --- 处理方式：删除含有空值的行 ---
before_rows = df_store.shape[0]
df_store.dropna(inplace=True)
after_rows = df_store.shape[0]
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

# --- 验证：是否还有空值 ---
print("\n【空值处理后验证】")
print(df_store.isnull().sum().sum())  # 0 表示处理完毕
print('======================')
print('======================')

# ======================================
# 3️⃣ 检查并处理重复值
# ======================================
print("\n【重复值检测】")
print(df_store.duplicated().sum())
print('================================')

if df_store.duplicated().sum() > 0:
    print(df_store[df_store.duplicated()])

df_store.drop_duplicates(inplace=True)

print("\n【重复值处理后验证】")
print(df_store.duplicated().sum())
print('======================')
print('======================')
print('=============================')

# ======================================
# 4️⃣ 识别并修正异常值
# ======================================
print("\n【数值列统计描述】")
print(df_store.describe(include='all'))

print('门店ID--------------------------------------')
outer = '门店ID'
if outer in df_store.columns:
    # 转换为字符串，防止类型错误
    df_store[outer] = df_store[outer].astype(str).str.strip()

    # 检测异常：空字符串 或 含有非法字符（非字母数字）
    outliers = df_store[
        (df_store[outer].isnull()) |
        (df_store[outer].str.len() == 0) |
        (~df_store[outer].str.match(r'^[A-Za-z0-9]+$'))
    ]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为最常见值（mode）
    if not df_store[outer].mode().empty:
        mode_value = df_store[outer].mode()[0]
        df_store.loc[outliers.index, outer] = mode_value
        print(f"\n【异常值处理】已将 {len(outliers)} 个异常ID替换为最常见值 {mode_value}。")
    else:
        print("\n【异常值处理】未检测到有效的众数，未进行替换。")

print('======================================')
print('======================================')

# ======================================
# 6️⃣ 导出清洗后的数据
# ======================================
clean_path = '../../data/cleared data/餐饮连锁数据_门店信息_cleaned.xlsx'
df_store.to_excel(clean_path, index=False)
print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")
